
/**
SharePoint Analytics

Installation

-Download Script and execute on your non production environment containing your sharepoint databases.
-Installation will create a new database called 'MetricsOps' and set both Data and Log to autogrow
-Installation will provision the tables and stored procedures needed to start collecting data right away!
-To Gather data run all 5 Stored Procedures in the metricsops database (cb_q1_proc, cb_q2_proc, cb_q3_proc, cb_q4_proc, cb_q5_proc)
-These 5 stored procedures will populate your metricsops database

USE

use metricsops
exec cb_q1_proc
exec cb_q2_proc
exec cb_q3_proc
exec cb_q4_proc
exec cb_q5_proc


**/


GO
/** MetricsOPs Database Creation **/
SET NOCOUNT ON
GO
IF Exists (select name from master..sysdatabases where name = 'metricsops')
PRINT 'Metricsops Database allready exists - Skipping Creation.'
ELSE
BEGIN
DECLARE @CMDSTR6 NVARCHAR(4000)
SELECT @CMDSTR6 = '
create database metricsops
alter database metricsops
Modify file
(name = metricsops,
 filegrowth = 10%);
alter database metricsops
modify file
(name = metricsops_log,
filegrowth = 10%)
'
EXEC (@cmdstr6)
PRINT 'Metricsops Database Created.'
END



GO
/** docextensions table Creation **/
SET NOCOUNT ON
GO
USE METRICSOPS
GO
IF Exists (select name from metricsops..sysobjects where name = 'docextensions')
PRINT 'docextensions table allready exists - Skipping Creation.'
ELSE
BEGIN
DECLARE @CMDSTR7 NVARCHAR(4000)
SELECT @CMDSTR7 = '


CREATE TABLE metricsops.[dbo].[docextensions](
	[extension] [nvarchar](20) COLLATE Latin1_General_CI_AS_KS_WS NULL
) ON [PRIMARY]


insert into metricsops..docextensions values (''ascx'')
insert into metricsops..docextensions values (''asp'')
insert into metricsops..docextensions values (''aspx'')
insert into metricsops..docextensions values (''dot'')
insert into metricsops..docextensions values (''eml'')
insert into metricsops..docextensions values (''exch'')
insert into metricsops..docextensions values (''jhtml'')
insert into metricsops..docextensions values (''jsp'')
insert into metricsops..docextensions values (''msg'')
insert into metricsops..docextensions values (''mspx'')
insert into metricsops..docextensions values (''nsf'')
insert into metricsops..docextensions values (''odc'')
insert into metricsops..docextensions values (''php'')
insert into metricsops..docextensions values (''tif'')
insert into metricsops..docextensions values (''tiff'')
insert into metricsops..docextensions values (''url'')
insert into metricsops..docextensions values (''vdx'')
insert into metricsops..docextensions values (''vss'')
insert into metricsops..docextensions values (''vst'')
insert into metricsops..docextensions values (''vsx'')
insert into metricsops..docextensions values (''vtx'')
insert into metricsops..docextensions values (''xml'')
insert into metricsops..docextensions values (''xps'')
insert into metricsops..docextensions values (''htm'')
insert into metricsops..docextensions values (''html'')
insert into metricsops..docextensions values (''mht'')
insert into metricsops..docextensions values (''mhtml'')
insert into metricsops..docextensions values (''pdf'')
insert into metricsops..docextensions values (''xls'')
insert into metricsops..docextensions values (''xlsm'')
insert into metricsops..docextensions values (''xlsx'')
insert into metricsops..docextensions values (''vsd'')
insert into metricsops..docextensions values (''txt'')
insert into metricsops..docextensions values (''ppt'')
insert into metricsops..docextensions values (''pptm'')
insert into metricsops..docextensions values (''pptx'')
insert into metricsops..docextensions values (''doc'')
insert into metricsops..docextensions values (''docm'')
insert into metricsops..docextensions values (''docx'')
insert into metricsops..docextensions values (''pub'')

'
EXEC (@cmdstr7)
PRINT 'docextensions table created.'
END


GO
/** cb_q1 Table Creation **/
SET NOCOUNT ON
GO
USE METRICSOPS
GO
IF Exists (select name from metricsops..sysobjects where name = 'cb_q1')
PRINT 'cb_q1 table allready exists - Skipping Creation.'
ELSE
BEGIN
DECLARE @CMDSTR8 NVARCHAR(4000)
SELECT @CMDSTR8 = '

CREATE TABLE metricsops.[dbo].[cb_q1](
	[SiteUrl] [nvarchar](256) COLLATE Latin1_General_CI_AS_KS_WS NOT NULL,
	[SiteAdmin] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NOT NULL,
	[recyclebin] [decimal](38, 6) NULL,
	[BandwidthUsed] [decimal](29, 11) NULL,
	[SiteSize] [decimal](29, 11) NULL,
	[SiteMaxQuota] [decimal](29, 11) NULL,
	[SiteID] [uniqueidentifier] NOT NULL,
	[Content_DB] [nvarchar](128) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ServerName] [nvarchar](128) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[LastContentChange] [datetime] NOT NULL,
	[DaysSinceLastChange] [int] NULL
) ON [PRIMARY]

'
EXEC (@cmdstr8)
PRINT 'cb_q1 table created.'
END



GO
/** cb_q2 Table Creation **/
SET NOCOUNT ON
GO
USE METRICSOPS
GO
IF Exists (select name from metricsops..sysobjects where name = 'cb_q2')
PRINT 'cb_q2 table allready exists - Skipping Creation.'
ELSE
BEGIN
DECLARE @CMDSTR9 NVARCHAR(4000)
SELECT @CMDSTR9 = '

CREATE TABLE metricsops.[dbo].[cb_q2](
	[ConfigServer] [nvarchar](128) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[config_db] [nvarchar](128) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[content_db] [nvarchar](128) COLLATE Latin1_General_CI_AS_KS_WS NOT NULL,
	[SiteID] [uniqueidentifier] NOT NULL,
	[servername] [nvarchar](128) COLLATE Latin1_General_CI_AS_KS_WS NOT NULL,
	[version] [varchar](2) COLLATE Latin1_General_CI_AS_KS_WS NOT NULL,
	[Siteurl] [nvarchar](263) COLLATE Latin1_General_CI_AS_KS_WS NOT NULL

) ON [PRIMARY]


'
EXEC (@cmdstr9)
PRINT 'cb_q2 table created.'
END


GO
/** cb_q3 Table Creation **/
SET NOCOUNT ON
GO
USE METRICSOPS
GO
IF Exists (select name from metricsops..sysobjects where name = 'cb_q3')
PRINT 'cb_q3 table allready exists - Skipping Creation.'
ELSE
BEGIN
DECLARE @CMDSTR10 NVARCHAR(4000)
SELECT @CMDSTR10 = '

CREATE TABLE metricsops.[dbo].[cb_q3](
	[ServerInFarm] [nvarchar](128) COLLATE Latin1_General_CI_AS_KS_WS NOT NULL,
	[config_DB] [nvarchar](128) COLLATE Latin1_General_CI_AS_KS_WS NULL

) ON [PRIMARY]
'
EXEC (@cmdstr10)
PRINT 'cb_q3 table created.'
END



GO
/** cb_q4 Table Creation **/
SET NOCOUNT ON
GO
USE METRICSOPS
GO
IF Exists (select name from metricsops..sysobjects where name = 'cb_q4')
PRINT 'cb_q4 table allready exists - Skipping Creation.'
ELSE
BEGIN
DECLARE @CMDSTR11 NVARCHAR(4000)
SELECT @CMDSTR11 = '
CREATE TABLE metricsops.[dbo].[cb_q4](
	[servername] [varchar](255) COLLATE Latin1_General_CI_AS_KS_WS NOT NULL,
	[Content_db] [varchar](255) COLLATE Latin1_General_CI_AS_KS_WS NOT NULL,
	[siteid] [uniqueidentifier] NOT NULL,
	[WebID] [uniqueidentifier] NOT NULL,
	[WebURL] [nvarchar](256) COLLATE Latin1_General_CI_AS_KS_WS NOT NULL,
	[parentwebid] [uniqueidentifier] NULL,
	[webtemplate] [int] NOT NULL,
	[language] [int] NOT NULL,
	[productversion] [smallint] NOT NULL,
	[DocumentCount] [int] NULL,
	[extension] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[docsize] [bigint] NULL
) ON [PRIMARY]
'
EXEC (@cmdstr11)
PRINT 'cb_q4 table created.'
END



GO
/** cb_q5 Table Creation **/
SET NOCOUNT ON
GO
USE METRICSOPS
GO
IF Exists (select name from metricsops..sysobjects where name = 'cb_q5')
PRINT 'cb_q5 table allready exists - Skipping Creation.'
ELSE
BEGIN
DECLARE @CMDSTR12 NVARCHAR(4000)
SELECT @CMDSTR12 = '
CREATE TABLE metricsops.[dbo].[cb_q5](
	[dbsize] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[unallocated] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[content_db] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[servername] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

'
EXEC (@cmdstr12)
PRINT 'cb_q5 table created.'
END



/** Query 1 Final for codeplex. **/
SET NOCOUNT ON
GO
USE Metricsops
GO
IF Exists (select name from sysobjects where name = 'cb_q1_proc')
PRINT 'cb_q1_proc already exists - Skipping Creation.'
ELSE
BEGIN
DECLARE @CMDSTR1 NVARCHAR(4000)
SELECT @CMDSTR1 = 
'create procedure cb_q1_proc  
as  
truncate table metricsops..cb_q1 
declare @cmdStr NVARCHAR(2000)  
SELECT @cmdStr = ''use [?]''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''''namevaluepair'''')''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''BEGIN''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''INSERT INTO metricsops..cb_q1''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''select a.fullurl as [SiteUrl],''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''b.tp_login as [SiteAdmin],''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''sum(cast(c.size as decimal))/1024/1024 as [recyclebin],''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''cast(d.bwused as decimal)/1024/1024 as [BandwidthUsed],''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''cast(d.diskused as decimal)/1024/1024 as [SiteSize],''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''cast(d.diskquota as decimal)/1024/1024 as [SiteMaxQuota],''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''d.id as [SiteID],(select db_name(dbid) from master..sysprocesses where spid=@@SPID) as [Content_DB],''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''(select @@servername) as [ServerName],'' 
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''d.lastcontentchange as [LastContentChange],''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''(select datediff(day,d.lastcontentchange,current_timestamp)) as [DaysSinceLastChange]''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''from webs as a inner join''   
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''sites as d on a.siteid=d.id inner join'' 
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''userinfo as b on a.siteid=b.tp_siteid left join''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''recyclebin as c on a.siteid=c.siteid where b.tp_siteadmin = ''''1'''' and a.parentwebid is null''    
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''group by a.fullurl, b.tp_login, d.diskused, d.id, d.bwused, d.diskquota, d.lastcontentchange''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''END''
exec master..sp_msforeachdb @command1 = @CMDSTR
'
exec (@CMDSTR1)
Print 'CB_Q1_Proc Stored Procedure Created.'
END




GO
/** Query 2 Final for CodePlex **/
SET NOCOUNT ON
USE Metricsops
GO
IF Exists (select name from sysobjects where name = 'cb_q2_proc')
PRINT 'cb_q2_proc already exists - Skipping Creation.'
ELSE
BEGIN
DECLARE @CMDSTR2 NVARCHAR(4000)
SELECT @CMDSTR2 = '
create procedure cb_q2_proc  
as  
truncate table metricsops..cb_q2 
declare @cmdStr NVARCHAR(2000)
SELECT @cmdStr = ''use [?]''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''''sitecounts'''')''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''BEGIN'' 
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''INSERT INTO metricsops..cb_q2''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''SELECT (Select @@servername) as [ConfigServer], (select db_name(dbid) from master..sysprocesses where spid=@@SPID) AS [config_db], a.name as [content_db],c.id as [SiteID],  b.name as [servername], ''''v3'''' as [version], ''''http://''''+d.name+c.path as [Siteurl]''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''from objects as a inner join''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''sitemap  as c on a.id=c.databaseid inner join''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''objects  as d on c.applicationid=d.id inner join''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''objects as F on a.parentid=f.id inner join''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''objects as b on f.parentid=b.id where a.id in (select databaseid from sitecounts)''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''END''
exec master..sp_msforeachdb @command1 = @CMDSTR
'
exec (@CMDSTR2)
Print 'CB_Q2_Proc Stored Procedure Created.'
END





GO
/** Query 3 Final for CodePlex **/
SET NOCOUNT ON
USE Metricsops
GO
IF Exists (select name from sysobjects where name = 'cb_q3_proc')
PRINT 'cb_q3_proc already exists - Skipping Creation.'
ELSE
BEGIN
DECLARE @CMDSTR3 NVARCHAR(4000)
SELECT @CMDSTR3 = '
create procedure cb_q3_proc  
as  
truncate table metricsops..cb_q3 
declare @cmdStr NVARCHAR(2000)
SELECT @cmdStr = ''use [?]''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''''sitecounts'''')''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''BEGIN''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''INSERT INTO metricsops..cb_q3''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''select name as [ServerInFarm], (select db_name(dbid) from master..sysprocesses where spid=@@SPID) as [config_DB]''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''from objects where''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''parentid in (select id from objects where name = (select db_name(dbid) from master..sysprocesses where spid=@@SPID))'' 
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''and classid = ''''E77AAF47-3CAC-4001-BC6B-5BCCB6486318'''' ''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''END''
exec master..sp_msforeachdb @command1 = @CMDSTR
'
exec (@CMDSTR3)
Print 'CB_Q3_Proc Stored Procedure Created.'
END






GO
/** Query 4 Final for CodePlex **/
SET NOCOUNT ON
USE Metricsops
GO
IF Exists (select name from sysobjects where name = 'cb_q4_proc')
PRINT 'cb_q4_proc already exists - Skipping Creation.'
ELSE
BEGIN
DECLARE @CMDSTR4 NVARCHAR(4000)
SELECT @CMDSTR4 = '
create procedure cb_q4_proc    
as    
truncate table metricsops..cb_q4   
DECLARE @cmdStr NVARCHAR(2000),
@servername SYSNAME

set @servername = @@servername
SELECT @cmdStr = ''use [?]''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''''namevaluepair'''')''   
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''BEGIN''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''INSERT INTO metricsops..cb_q4''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''select distinct '''''' + @servername + '''''' [servername],(select db_name(dbid) from master..sysprocesses where spid=@@SPID) [Content_db],'' 
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''a.siteid, a.id as [WebID], a.fullurl [WebURL], a.parentwebid, a.webtemplate,'' 
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''a.[language], a.productversion,''   
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''count(cast(b.id as varchar(50))) as [DocumentCount],''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''b.extension, sum(cast(b.size as bigint)) [docsize]''    
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''from webs as a'' 
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''inner join alldocs as b on a.siteid=b.siteid and a.id=b.webid''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''where exists''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''(select m.extension from metricsops.dbo.docextensions m where m.extension = b.extension)'' 
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''group by a.siteid, a.id, a.fullurl, a.parentwebid, a.webtemplate,''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''a.[language], a.productversion, b.extension''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''END''
exec master..sp_msforeachdb @command1 = @CMDSTR
'
exec (@cmdstr4)
Print 'CB_Q4_Proc Stored Procedure Created.'
END







GO
/** Query 5 Final for CodePlex **/
SET NOCOUNT ON
USE Metricsops
GO
IF Exists (select name from sysobjects where name = 'cb_q5_proc')
PRINT 'cb_q5_proc already exists - Skipping Creation.'
ELSE
BEGIN
DECLARE @CMDSTR5 NVARCHAR(4000)
SELECT @CMDSTR5 = '
create procedure cb_q5_proc  
as  
truncate table metricsops..cb_q5  
declare @cmdStr NVARCHAR(2000)
SELECT @cmdStr = ''use [?]''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''BEGIN''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''INSERT INTO metricsops..cb_q5 (content_db, servername, dbsize, unallocated)''  
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''SELECT DB_NAME(DB_ID()) [CONTENT_DB], (SELECT @@SERVERNAME) [SERVERNAME],''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''LTRIM(STR((CONVERT(DECIMAL(15,2),(SELECT SUM(CONVERT(BIGINT,CASE WHEN status & 64  = 0 THEN [size] ELSE 0 END)) FROM dbo.sysfiles)) +''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''CONVERT(DECIMAL(15,2),(SELECT SUM(CONVERT(BIGINT,CASE WHEN status & 64 <> 0 THEN [size] ELSE 0 END)) FROM dbo.sysfiles)))*8192/1048576,15,2)) [dbSize],'' 
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''LTRIM(STR((CASE WHEN (SELECT SUM(CONVERT(BIGINT,CASE WHEN status & 64  = 0 THEN [size] ELSE 0 END)) FROM dbo.sysfiles)'' 
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''>= (SELECT SUM(a.total_pages) FROM  sys.partitions p''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''JOIN sys.allocation_units a ON p.partition_id = a.container_id''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''LEFT JOIN sys.internal_tables it ON p.[object_id]  = it.[object_id])''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''THEN (CONVERT(DECIMAL(15,2),(SELECT SUM(CONVERT(BIGINT,CASE WHEN status & 64  = 0 THEN [size] ELSE 0 END)) FROM dbo.sysfiles)) -''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''CONVERT(DECIMAL(15,2),(SELECT SUM(a.total_pages) FROM  sys.partitions p''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''JOIN sys.allocation_units a ON p.partition_id = a.container_id''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''LEFT JOIN sys.internal_tables it ON p.[object_id]  = it.[object_id])))*8192/1048576''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''ELSE 0 END),15,2)) [unallocated]''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + ''END''
exec master..sp_msforeachdb @command1 = @CMDSTR
'
exec (@cmdstr5)
Print 'CB_Q5_Proc Stored Procedure Created.'
END



